﻿// Stickers
// An example app demonstrating cloud native .NET apps
// Written by daxnet (Sunny Chen)
// MIT License
// 2024

using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Dapper;
using Npgsql;
using Stickers.Common;
using Stickers.Common.DataAccess;

namespace Stickers.DataAccess.PostgreSQL;

/// <summary>
///     Represents the Simplified Data Accessor that utilizes PostgreSQL as the backend database.
/// </summary>
/// <param name="connectionString">The Connection String used for connecting to the PostgreSQL database.</param>
public class PostgreSqlDataAccessor(string connectionString) : ISimplifiedDataAccessor
{
    public async Task<int> AddAsync<TEntity>(string tenantId, TEntity entity, CancellationToken cancellationToken = default)
        where TEntity : class, IEntity
    {
        var tableName = GetTableName<TEntity>(tenantId);

        var tableFieldNames = string.Join(", ",
            GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
                .Select(n => $"\"{n.Value}\""));

        var fieldValueNames = string.Join(", ",
            GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id))
                .Select(n => $"@{n.Key}"));

        var sql = $@"INSERT INTO {tableName} ({tableFieldNames}) VALUES ({fieldValueNames}) RETURNING ""Id""";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        var id = await sqlConnection.ExecuteScalarAsync<int>(sql, entity);
        entity.Id = id;
        return id;
    }

    public async Task<int> RemoveByIdAsync<TEntity>(string tenantId, int id, CancellationToken cancellationToken = default)
        where TEntity : class, IEntity
    {
        var sql = $@"DELETE FROM {GetTableName<TEntity>(tenantId)} WHERE ""Id"" = @Id";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        return await sqlConnection.ExecuteAsync(sql, new { Id = id });
    }

    public async Task<TEntity?> GetByIdAsync<TEntity>(string tenantId, int id, CancellationToken cancellationToken = default)
        where TEntity : class, IEntity
    {
        var sql = $@"SELECT {GetQueryFields<TEntity>()} FROM {GetTableName<TEntity>(tenantId)} WHERE ""Id"" = @Id";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        return await sqlConnection.QuerySingleOrDefaultAsync<TEntity>(sql, new { Id = id });
    }

    public async Task<int> UpdateAsync<TEntity>(string tenantId, int id, TEntity entity, CancellationToken cancellationToken = default)
        where TEntity : class, IEntity
    {
        var updatingFields = GetColumnNames<TEntity>(p => p.Name == nameof(IEntity.Id));
        var updateSetStatement = string.Join(", ", updatingFields.Select(kv => $@"""{kv.Value}""=@{kv.Key}"));
        var sql = $@"UPDATE {GetTableName<TEntity>(tenantId)} SET {updateSetStatement} WHERE ""Id"" = @Id";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        return await sqlConnection.ExecuteAsync(sql, entity);
    }

    public async Task<Paginated<TEntity>> GetPaginatedEntitiesAsync<TEntity, TField>(string tenantId, 
        Expression<Func<TEntity, TField>> orderByExpression, bool sortAscending = true,
        int pageSize = 25, int pageNumber = 1, Expression<Func<TEntity, bool>>? filterExpression = null,
        CancellationToken cancellationToken = default) where TEntity : class, IEntity
    {
        var filter = filterExpression == null ? "" : BuildSqlWhereClause(filterExpression.Body);
        var sort = BuildSqlFieldName(orderByExpression.Body);
        return await ReadPaginatedEntitiesAsync<TEntity>(tenantId, filter, sort, sortAscending, pageSize, pageNumber);
    }

    public async Task<bool> ExistsAsync<TEntity>(string tenantId, 
        Expression<Func<TEntity, bool>> filterExpression,
        CancellationToken cancellationToken = default) where TEntity : class, IEntity
    {
        var sqlWhereStatement = BuildSqlWhereClause(filterExpression.Body);
        var sql = $"SELECT COUNT(*) FROM {GetTableName<TEntity>(tenantId)} WHERE {sqlWhereStatement}";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        var count = await sqlConnection.ExecuteScalarAsync<int>(sql);
        return count > 0;
    }

    private static string GetTableName<TEntity>(string tenantId) where TEntity : class, IEntity
    {
        return typeof(TEntity).IsDefined(typeof(TableAttribute), false)
            ? $"{tenantId}.{typeof(TEntity).GetCustomAttribute<TableAttribute>()?.TableName ?? typeof(TEntity).Name}"
            : $"{tenantId}.{typeof(TEntity).Name}";
    }

    private static IEnumerable<KeyValuePair<string, string>> GetColumnNames<TEntity>(
        params Predicate<PropertyInfo>[] excludes)
        where TEntity : class, IEntity
    {
        return from p in typeof(TEntity).GetProperties()
            where p.CanRead && p.CanWrite && !excludes.Any(pred => pred(p))
            select p.IsDefined(typeof(FieldAttribute), false)
                ? new KeyValuePair<string, string>(p.Name, p.GetCustomAttribute<FieldAttribute>()?.FieldName ?? p.Name)
                : new KeyValuePair<string, string>(p.Name, p.Name);
    }

    private static string BuildSqlFieldName(Expression expression)
    {
        if (expression is not MemberExpression && expression is not UnaryExpression)
            throw new NotSupportedException("Expression is not a member expression");

        var memberExpression = expression switch
        {
            MemberExpression expr => expr,
            UnaryExpression { NodeType: ExpressionType.Convert } unaryExpr =>
                (MemberExpression)unaryExpr.Operand,
            _ => null
        };

        if (memberExpression is null)
            throw new NotSupportedException("Can't infer the member expression from the given expression.");

        return memberExpression.Member.IsDefined(typeof(FieldAttribute), false)
            ? memberExpression.Member.GetCustomAttribute<FieldAttribute>()?.FieldName ??
              memberExpression.Member.Name
            : memberExpression.Member.Name;
    }

    private static string BuildSqlWhereClause(Expression expression)
    {
        if (expression is not BinaryExpression binaryExpression)
            throw new NotSupportedException("The filter expression is not supported.");

        var oper = binaryExpression.NodeType switch
        {
            ExpressionType.Equal => "=",
            ExpressionType.NotEqual => "<>",
            ExpressionType.GreaterThan => ">",
            ExpressionType.GreaterThanOrEqual => ">=",
            ExpressionType.LessThan => "<",
            ExpressionType.LessThanOrEqual => "<=",
            ExpressionType.AndAlso => " AND ",
            ExpressionType.OrElse => " OR ",
            _ => null
        };
        
        if (string.IsNullOrEmpty(oper)) throw new NotSupportedException("The filter expression is not supported.");
        if (string.Equals(oper, " AND ") || string.Equals(oper, " OR "))
        {
            var leftStr = BuildSqlWhereClause(binaryExpression.Left);
            var rightStr = BuildSqlWhereClause(binaryExpression.Right);
            return string.Concat(leftStr, oper, rightStr);
        }

        if (binaryExpression is not { Left: MemberExpression leftMemberExpression })
        {
            throw new NotSupportedException("The filter expression is not supported.");
        }
        
        var fieldName = BuildSqlFieldName(leftMemberExpression);
        string? fieldValue = null;
        switch (binaryExpression.Right)
        {
            case ConstantExpression rightConstExpression:
                fieldValue = FormatValue(rightConstExpression.Value);
                break;
            case MemberExpression rightMemberExpression:
                var rightConst = rightMemberExpression.Expression as ConstantExpression;
                var member = rightMemberExpression.Member.DeclaringType;
                if (rightMemberExpression.Member.MemberType == MemberTypes.Property)
                {
                    fieldValue = FormatValue(member
                        ?.GetProperty(rightMemberExpression.Member.Name,
                            BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase |
                            BindingFlags.NonPublic)
                        ?.GetValue(rightConst?.Value));
                }
                else
                {
                    fieldValue = FormatValue(member?.GetField(rightMemberExpression.Member.Name)
                        ?.GetValue(rightConst?.Value));
                }

                break;
        }

        if (!string.IsNullOrEmpty(fieldValue))
            return $"""
                    "{fieldName}" {oper} {fieldValue}
                    """;

        throw new NotSupportedException("The filter expression is not supported.");

        string? FormatValue(object? value)
        {
            return value switch
            {
                null => null,
                string => $"'{value}'",
                _ => value.ToString()
            };
        }
    }

    private static string GetQueryFields<TEntity>(params Predicate<PropertyInfo>[] excludes)
        where TEntity : class, IEntity
    {
        var columnNames = GetColumnNames<TEntity>(excludes);
        return string.Join(", ", columnNames.Select(p => $"""
                                                          "{p.Value}" {p.Key}
                                                          """));
    }

    private async Task<Paginated<TEntity>> ReadPaginatedEntitiesAsync<TEntity>(string tenantId, 
        string filterExpression = "",
        string sortExpression = "Id", bool sortAscending = true, int pageSize = 25, int pageNumber = 1)
        where TEntity : class, IEntity
    {
        var sqlBuilder = new StringBuilder();
        sqlBuilder.Append(
            $"""SELECT {GetQueryFields<TEntity>()}, COUNT(*) OVER() AS "TotalCount" FROM {GetTableName<TEntity>(tenantId)} """);
        if (!string.IsNullOrEmpty(filterExpression)) sqlBuilder.Append($@"WHERE {filterExpression} ");

        if (!string.IsNullOrEmpty(sortExpression))
        {
            sqlBuilder.Append($@"ORDER BY ""{sortExpression}"" ");
            if (!sortAscending) sqlBuilder.Append("DESC ");
        }

        var offset = (pageNumber - 1) * pageSize;
        sqlBuilder.Append($"OFFSET {offset} LIMIT {pageSize}");
        await using var connection = new NpgsqlConnection(connectionString);
        var sql = sqlBuilder.ToString();
        var entities = await connection.QueryAsync(sql);
        var result = new Paginated<TEntity>();
        var firstTaken = false;
        foreach (var entity in entities)
        {
            if (!firstTaken)
            {
                var totalCount = entity.TotalCount;
                var totalPages = Convert.ToInt32(Math.Ceiling(totalCount * 1.0F / pageSize));
                result.TotalPages = totalPages;
                result.TotalCount = totalCount;
                firstTaken = true;
            }

            result.Items.Add(BuildEntityFromDictionary<TEntity>(entity));
        }

        result.PageIndex = pageNumber;
        result.PageSize = pageSize;

        return result;
    }

    private static TEntity? BuildEntityFromDictionary<TEntity>(IDictionary<string, object> dictionary)
        where TEntity : class, IEntity
    {
        var properties = from p in typeof(TEntity).GetProperties()
            where p.CanRead && p.CanWrite && dictionary.ContainsKey(p.Name.ToLower())
            select p;
        var obj = (TEntity?)Activator.CreateInstance(typeof(TEntity));
        foreach (var property in properties) property.SetValue(obj, dictionary[property.Name.ToLower()]);

        return obj;
    }
}